package com.eascs.admin.o2o.badloan.service;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.eascs.admin.api.form.O2oAfterLoanForm;
import com.eascs.admin.o2o.badloan.dao.O2oBadLoanDao;
import com.eascs.admin.o2o.badloan.entity.O2oBadLoanPageVo;
import com.eascs.admin.o2o.badloan.entity.O2oBadRecordEntity;
import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataEnity;
import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataTdEnity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.loan.form.O2oLoanForm;
import com.eascs.loan.form.O2oPaymentsForm;

/**
 * 
 * @author CXW
 *
 */
@Service
public class O2oBadLoanService {

	@Autowired
	O2oBadLoanDao badLoanDao;

	/**
	 * @Description:
	 * 				<p>
	 *               分页查询不良贷款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:09:39
	 * @version V1.0
	 */
	public PageResult<Object[]> getByPage(O2oBadLoanPageVo vo) {
		return badLoanDao.getByPage(vo);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               分页查询分期借力贷
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:09:39
	 * @version V1.0
	 */
	public PageResult<Object[]> getByFqJl(O2oBadLoanPageVo vo) {
		return badLoanDao.getByFqJl(vo);
	}

	public PageResult<Object[]> getByPageExport(O2oBadLoanPageVo vo) {
		return badLoanDao.getByPageExport(vo);
	}

	public PageResult<Object[]> getByFqJlPageExport(O2oBadLoanPageVo vo) {
		return badLoanDao.getByFqJlPageExport(vo);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据loanDataId查询不良贷款记录信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:10:01
	 * @version V1.0
	 */
	public O2oEaLoanDataEnity findByLoanDataId(String loanDataId) {
		return badLoanDao.findByBillCode(loanDataId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据billCode查询不良贷款记录信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:10:01
	 * @version V1.0
	 */
	public O2oEaLoanDataEnity findDataByBillCode(String billCode) {
		return badLoanDao.findDataByBillCode(billCode);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据id查询不良贷款记录信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:10:01
	 * @version V1.0
	 */
	public O2oEaLoanDataEnity findById(String id) {
		return badLoanDao.getById(O2oEaLoanDataEnity.class, id);
	}
	
	/**
	 * @Description:
	 * 				<p>
	 *               根据id或回款记录id查询代偿详情
	 *               </p>
	 * @author CXW
	 * @date 2017年3月31日 下午2:29:30
	 * @version V1.0
	 */
	public O2oEaLoanDataEnity findByIdOrRecordIdDetail(O2oLoanForm loanForm) {
		String recordSql = "select obr.* from o2o_badloan_record obr where obr.id=?";
		String eaBankSql = "select oeld.* from o2o_ea_loan_data oeld where oeld.id=?";
		String loanDataId = "";
		if (!StringUtils.isEmpty(loanForm.getBackCordId())) {
			O2oBadRecordEntity badRecordEntity = badLoanDao.findEntity(recordSql, O2oBadRecordEntity.class, loanForm.getBackCordId());
			loanDataId = badRecordEntity.getLoanDataId();
		} else {
			loanDataId = loanForm.getLoanDataId();
		}
		return badLoanDao.findEntity(eaBankSql, O2oEaLoanDataEnity.class, loanDataId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据还款计划id查询不良贷款还款计划
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:10:45
	 * @version V1.0
	 */
	public O2oEaLoanDataTdEnity findDataTdById(String id) {
		return badLoanDao.getById(O2oEaLoanDataTdEnity.class, id);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据贷款单号查询不良贷款还款计划
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:11:25
	 * @version V1.0
	 */
	public O2oEaLoanDataTdEnity findDataTdByBillCode(String billCode) {
		return badLoanDao.findDataTdByBillCode(billCode);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               分页查询还款计划
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:23:49
	 * @version V1.0
	 */
	public PageResult<Object[]> paymentsList(O2oPaymentsForm vo) {
		return badLoanDao.paymentsList(vo);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               代偿分期分期还款计划
	 *               </p>
	 * @author CXW
	 * @date 2017年3月15日 下午2:54:33
	 * @version V1.0
	 */
	public PageResult<Object[]> backPlan(O2oPaymentsForm vo) {
		return badLoanDao.backPlan(vo);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               插入还款计划
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:24:35
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int insert(O2oEaLoanDataTdEnity dataTdEnity) {
		return badLoanDao.insert(dataTdEnity);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               更新EA放款表
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:24:35
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateEaLoanData(O2oEaLoanDataEnity dataEnity) {
		return badLoanDao.updateById(dataEnity);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               插入还款记录表
	 *               </p>
	 * @author CXW
	 * @date 2017年02月20日 上午15:09:35
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int insertRecord(O2oBadRecordEntity badRecordEntity) {
		return badLoanDao.insert(badRecordEntity);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据还款记录id查询还款记录信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:08:24
	 * @version V1.0
	 */
	public O2oBadRecordEntity getByBadRecordID(String id) {
		return badLoanDao.getById(O2oBadRecordEntity.class, id);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据贷款单号查询还款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:07:50
	 * @version V1.0
	 */
	public List<O2oBadRecordEntity> findRecordByBillCode(String billCode, String loanDataId) {
		return badLoanDao.findRecordByBillCode(billCode, loanDataId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               查询已还本金、利息、当前欠款余额
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午9:57:03
	 * @version V1.0
	 */
	public O2oBadRecordEntity findRecordTotal(String billCode, String loanDataId) {
		return badLoanDao.findRecordTotal(billCode, loanDataId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据贷款单号和流程状态查询回款记录
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午9:57:03
	 * @version V1.0
	 */
	public O2oBadRecordEntity findRecordByFlowStatus(O2oBadRecordEntity o2oBadRecordEntity) {
		return badLoanDao.findRecordByFlowStatus(o2oBadRecordEntity);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               更新还款记录信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:08:58
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateByBadRecordID(O2oBadRecordEntity entity) {
		return badLoanDao.updateById(entity);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               插入还款记录信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月22日 上午10:09:12
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int deleteByBadRecordID(String id) {
		return badLoanDao.deleteById(O2oBadRecordEntity.class, id);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据贷款单号查询已还款次数
	 *               </p>
	 * @author CXW
	 * @date 2016年12月26日 下午3:27:02
	 * @version V1.0
	 */
	public int findCountByBillCode(String billCode) {
		String sql = "SELECT COUNT(*) FROM O2O_BADLOAN_RECORD WHERE FLOW_STATUS='CLO' AND IS_DELETE='N' AND BILL_CODE=?";
		return badLoanDao.findInteger(sql, billCode);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               根据贷款单号查询已上一次还款时间（实际收款时间）
	 *               </p>
	 * @author CXW
	 * @date 2016年12月26日 下午3:27:02
	 * @version V1.0
	 */
	public String findPayDateByBillCode(String billCode) {
		String sql = "SELECT MAX(THIS_PAYMENT_DATE) FROM O2O_BADLOAN_RECORD WHERE FLOW_STATUS='CLO' AND IS_DELETE='N' AND BILL_CODE=?";
		return badLoanDao.findString(sql, billCode);
	}

	/**
	 * @Description:
	 * 				<p>
	 * 
	 *               </p>
	 * @author CXW
	 * @date 2017年1月4日 下午2:30:49
	 * @version V1.0
	 */
	public PageResult<Object[]> findByPageRecord(O2oBadLoanPageVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT obr.id,obr.bill_code,oeld.bill_code AS bill_code_bk,opr.province_name,");
		querySql.append(" oeld.loaner_name,CONCAT(y.ltd_code,'-',y.ltd_name) as ltd_name,oeld.loan_type,obr.this_payment_principal,");
		querySql.append(" DATE_FORMAT(obr.new_time,'%Y-%m-%d') AS new_time, ");
		querySql.append(" DATE_FORMAT(obr.this_payment_date,'%Y-%m-%d') AS this_payment_date,obr.liquidate_name,obr.flow_status ");
		querySql.append(" FROM o2o_badloan_record AS obr INNER JOIN o2o_ea_loan_data AS oeld ON ");
		querySql.append(" obr.bill_code = oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on ");
		querySql.append(" oeld.ltd = opr.platform_id LEFT JOIN o2o_loanapply y on y.bill_code=obr.bill_code where /**DATA_PERMISSION(oeld.ltd)**/ obr.is_delete='N' ");
		if (!StringUtils.isEmpty(vo.getLoanDataId())) {
			querySql.append(" AND obr.loan_data_id = ? ");
			sqlPageQuery.addParam(vo.getLoanDataId());
		}
		if (!StringUtils.isEmpty(vo.getBillCode())) {
			querySql.append(" AND obr.bill_code like ? ");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (!StringUtils.isEmpty(vo.getLoanType())) {
			querySql.append(" AND oeld.loan_type = ? ");
			sqlPageQuery.addParam(vo.getLoanType());
		}
		if (!StringUtils.isEmpty(vo.getLoanerName())) {
			querySql.append(" AND oeld.loaner_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		if (!StringUtils.isEmpty(vo.getProvince())) {
			querySql.append(" AND opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}
		if (!StringUtils.isEmpty(vo.getFirstTime())) {
			querySql.append(" AND obr.this_payment_date >= ? ");
			sqlPageQuery.addParam(vo.getFirstTime());
		}
		if (!StringUtils.isEmpty(vo.getEndTime())) {
			querySql.append(" AND obr.this_payment_date < ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(vo.getEndTime()));
			gc.add(5, 1);
			sqlPageQuery.addParam(gc.getTime());
		}

		if (!StringUtils.isEmpty(vo.getStatus())) {
			querySql.append(" AND obr.flow_status = ? ");
			sqlPageQuery.addParam(vo.getStatus());
		}
		if (!StringUtils.isEmpty(vo.getLiquiName())) {
			querySql.append(" AND obr.liquidate_name = ? ");
			sqlPageQuery.addParam(vo.getLiquiName());
		}
		querySql.append(" GROUP BY obr.id ORDER BY obr.flow_end_time DESC");
		sqlPageQuery.setQuerySql(querySql.toString());
		return badLoanDao.findDataWithPage(sqlPageQuery);
	}

	public PageResult<Object[]> findByPageExport(O2oBadLoanPageVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT obr.id,obr.bill_code,oeld.bill_code AS bill_code_bk,opr.province_name,");
		querySql.append(" oeld.loaner_name,CONCAT(y.ltd_code,'-',y.ltd_name) as ltd_name,");
		querySql.append(" (SELECT pdo.item_name FROM pub_dditem_o2o AS pdo WHERE pdo.item_code = oeld.loan_type AND pdo.dd_no = 'lhdch77b1ac1d') as loan_type,");
		querySql.append(" obr.this_payment_principal,DATE_FORMAT(obr.new_time,'%Y-%m-%d') AS new_time, ");
		querySql.append(" DATE_FORMAT(obr.this_payment_date,'%Y-%m-%d') AS this_payment_date,obr.now_arrears,obr.flow_status ");
		querySql.append(" FROM o2o_badloan_record AS obr INNER JOIN o2o_ea_loan_data AS oeld ON ");
		querySql.append(" obr.bill_code = oeld.bill_code_ea  LEFT JOIN o2o_platform_regional opr on oeld.ltd = opr.platform_id LEFT JOIN o2o_loanapply y on y.bill_code=obr.bill_code where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 ");
		if (!StringUtils.isEmpty(vo.getLoanDataId())) {
			querySql.append(" AND obr.loan_data_id = ? ");
			sqlPageQuery.addParam(vo.getLoanDataId());
		}
		if (!StringUtils.isEmpty(vo.getBillCode())) {
			querySql.append(" AND obr.bill_code like ? ");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (!StringUtils.isEmpty(vo.getLoanType())) {
			querySql.append(" AND oeld.loan_type = ? ");
			sqlPageQuery.addParam(vo.getLoanType());
		}
		if (!StringUtils.isEmpty(vo.getLoanerName())) {
			querySql.append(" AND oeld.loaner_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		if (!StringUtils.isEmpty(vo.getProvince())) {
			querySql.append(" AND opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}
		if (!StringUtils.isEmpty(vo.getHostProvince())) {
			querySql.append(" AND opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}
		if (!StringUtils.isEmpty(vo.getFirstTime())) {
			querySql.append(" AND obr.new_time >= ? ");
			sqlPageQuery.addParam(vo.getFirstTime());
		}
		if (!StringUtils.isEmpty(vo.getEndTime())) {
			querySql.append(" AND obr.new_time < ? ");
			sqlPageQuery.addParam(vo.getEndTime());
		}
		querySql.append(" GROUP BY obr.id ORDER BY obr.flow_end_time DESC");
		sqlPageQuery.setQuerySql(querySql.toString());
		return badLoanDao.findDataWithPage(sqlPageQuery);
	}

	public List<Object[]> getLoanDataId() {
		String sql = "select obr.loan_data_id from o2o_badloan_record obr where flow_status='CLO' and is_delete='N' group by obr.bill_code order by obr.pay_time desc";
		return badLoanDao.findEntitys(sql, Object[].class);
	}

	public List<O2oBadRecordEntity> getBadRecord(String loanDataId) {
		String sql = "select obr.* from o2o_badloan_record obr where obr.loan_data_id=? and flow_status='CLO' and is_delete='N' order by obr.pay_time asc";
		return badLoanDao.findEntitys(sql, O2oBadRecordEntity.class, loanDataId);
	}

	public String getNowAreas(String billCode, String loanDataId) {
		return badLoanDao.getNowAreas(billCode, loanDataId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               更新截止最后一次还款后代偿余额
	 *               </p>
	 * @author CXW
	 * @date 2017年3月22日 下午1:51:35
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updatePayAreas(String loanDataId) {
		return badLoanDao.updatePayAreas(loanDataId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               更新所有最后一次回款余额
	 *               </p>
	 * @author CXW
	 * @date 2017年3月29日 上午10:59:11
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updatePayAreasAll() {
		StringBuffer sql = new StringBuffer();
		sql.append(" update o2o_ea_loan_data oeldr join ( ");
		sql.append(" SELECT obr.now_arrears,obr.pay_time,obr.loan_data_id ");
		sql.append(" from o2o_badloan_record obr join (SELECT MAX(obrr.pay_time) ");
		sql.append(" as pay_time,obrr.loan_data_id from o2o_badloan_record obrr ");
		sql.append(" where obrr.flow_status='CLO' and obrr.is_delete='N' GROUP BY  obrr.loan_data_id ");
		sql.append(" ) as tt on tt.loan_data_id=obr.loan_data_id and  ");
		sql.append(" tt.pay_time=obr.pay_time GROUP BY obr.loan_data_id) ");
		sql.append(" as ole on ole.loan_data_id=oeldr.id set oeldr.pay_areas = ole.now_arrears ");
		return badLoanDao.execute(sql.toString());
	}

	/**
	 * @Description:
	 * 				<p>
	 *               校正代偿回款总额
	 *               </p>
	 * @author CXW
	 * @date 2017年3月28日 下午1:36:31
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateLoad(String bankLoanId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" update o2o_ea_loan_data oeld set oeld.payment_amount=(SELECT ");
		sql.append(" sum(obr.pay_amount) from o2o_badloan_record obr ");
		sql.append(" where obr.loan_data_id=oeld.id and obr.flow_status='CLO' and obr.is_delete='N' ) ");
		sql.append(" where oeld.id=? ");
		return badLoanDao.execute(sql.toString(), bankLoanId);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               校正所有代偿回款总额
	 *               </p>
	 * @author CXW
	 * @date 2017年3月29日 上午9:56:43
	 * @version V1.0
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateLoadTatol() {
		StringBuffer sql = new StringBuffer();
		sql.append(" update o2o_ea_loan_data oeld set oeld.payment_amount=(SELECT ");
		sql.append(" sum(obr.pay_amount) from o2o_badloan_record obr ");
		sql.append(" where obr.loan_data_id=oeld.id and obr.flow_status='CLO' and obr.is_delete='N'  GROUP BY obr.loan_data_id) ");
		return badLoanDao.execute(sql.toString());
	}

	public PageResult<Object[]> getBadLoanPage(O2oBadLoanPageVo vo) {
		return badLoanDao.getBadLoanPage(vo);
	}

	/**
	 * @Title: getDcHkPage
	 * @Description: 代偿回款查询
	 * @author 张庆奇
	 * @param vo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws @date
	 *             2017年4月26日 上午10:13:08
	 */
	public PageResult<Object[]> getDcHkPage(O2oAfterLoanForm vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getO2oIndex());
		Calendar calendar = Calendar.getInstance();
		// 获得当前时间的月份，月份从0开始所以结果要加1
		int month = calendar.get(Calendar.MONTH) + 1;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT d.bill_code_ea,l.province_name,concat_ws('-',y.ltd_code,y.ltd_name) as ltdname,");
		sql.append(" d.loaner_name,ba.loan_date,y.end_bank_date,(SELECT ppd.item_name FROM pub_dditem ppd where ppd.dd_no='10340' and d.loan_bank=ppd.item_code) as bank_loan_name,oalfc.the_first_date,d.pay_amount,d.pay_date,");
		sql.append(" (SELECT IFNULL(sum(r.pay_amount),0)  from o2o_badloan_record r ");
		sql.append(" where date_format(r.this_payment_date,'%Y')=date_format(date_add(now(), interval -1 year), '%Y')");
		sql.append(" and r.loan_data_id=d.id and r.flow_status='CLO' ) as tot ,");
		for (int i = month - 1; i >= 0; i--) {
			int now_month = month - i;
			sql.append(" (SELECT IFNULL(sum(r.pay_amount),0)  from o2o_badloan_record r ");
			sql.append(" where date_format(r.this_payment_date,'%Y%m')=date_format(date_add(now(), interval -" + i + " month), '%Y%m')");
			sql.append("  and r.loan_data_id=d.id and  r.flow_status='CLO' ) as month_" + now_month + ",");
		}
		// 去除掉最后一个多余的逗号
		sql = new StringBuffer(sql.toString().substring(0, sql.toString().length() - 1));

		sql.append(" from o2o_ea_loan_data d LEFT JOIN o2o_platform_regional l on d.ltd=l.platform_id");
		sql.append(" LEFT JOIN o2o_bank_loan_data ba on d.bill_code_ea=ba.bill_code");// o2o_bank_loan_data--ba.loan_date放款日期
		sql.append(" left join o2o_loanapply y on d.bill_code_ea=y.bill_code ");
		sql.append(" left join o2o_after_loan_for_customer oalfc on oalfc.customer_id= y.loan_id ");
		sql.append(" where  d.pay_type='DF' ");
		
		if (StringUtil.hasText(vo.getBillCode())) {
			sql.append(" and d.bill_code_ea like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}

		if (StringUtil.hasText(vo.getLtd())) {
			sql.append(" and d.ltd = ?");
			sqlPageQuery.addParam(vo.getLtd());
		}

		if (StringUtil.hasText(vo.getLoanUserName())) {
			sql.append(" and  d.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanUserName());
		}
		if (!StringUtil.isEmpty(vo.getHostProvince())) {
			sql.append(" AND  l.province_code  = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}

		if (!StringUtil.isEmpty(vo.getLoanDateStart())) {
			sql.append(" AND d.loan_date >= ?");
			sqlPageQuery.addParam(vo.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(vo.getLoanDateEnd())) {
			sql.append(" AND d.loan_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getLoanDateEnd()), 1)));
		}
		if (!StringUtil.isEmpty(vo.getBankLoan())) {
			sql.append(" AND d.loan_bank = ?");
			sqlPageQuery.addParam(vo.getBankLoan());
		}

		if (!StringUtil.isEmpty(vo.getPayDateStart())) {
			sql.append(" AND d.pay_date >= ?");
			sqlPageQuery.addParam(vo.getPayDateStart());
		}
		if (!StringUtil.isEmpty(vo.getPayDateEnd())) {
			sql.append(" AND d.pay_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getPayDateEnd()), 1)));
		}

		sql.append(" order by d.pay_date desc ");
		sqlPageQuery.setQuerySql(sql.toString());
		return badLoanDao.findDataWithPage(sqlPageQuery);
	}

	public String findEndReloanTime(String recordId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT MAX(obr.this_payment_date) as this_payment_date ");
		sql.append("  from o2o_badloan_record obr where obr.loan_data_id=?");
		return badLoanDao.findString(sql.toString(), recordId);
	}

	public O2oEaLoanDataEnity findByIdDetailOreCordStatus(String id) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oeld.id,oll.loanapply_no,oeld.bill_code_ea,oeld.bill_code,opr.province_name, ");
		querySql.append(" oeld.loaner_name,CONCAT(oll.ltd_code,'-',oll.ltd_name)  as ltd_name,oeld.pay_type,oeld.loan_type, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date,'%Y-%m-%d'),oeld.pay_amount,oeld.payment_amount,DATE_FORMAT(");
		querySql.append(" (SELECT MAX(bbtr.this_payment_date) FROM o2o_badloan_record bbtr WHERE ");
		querySql.append(" bbtr.flow_status='CLO' and bbtr.bill_code= oeld.bill_code_ea and bbtr.loan_data_id=oeld.id),'%Y-%m-%d') as end_date,  ");
		querySql.append(" case oels.flow_status when 'CLO' then  '已结清' else '未结清' end as flow_status ");
		querySql.append(" from o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll on ");
		querySql.append(" oll.bill_code=oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(" LEFT JOIN o2o_ea_loan_settle oels on oeld.id=oels.loan_data_id ");
		querySql.append(" where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'DF' and oeld.id = ?");
		querySql.append(" order by oeld.oprate_time desc ");
		return badLoanDao.findEntity(querySql.toString(), O2oEaLoanDataEnity.class, id);
	}

}
